--ALTER DATABASE iic_data_convert SET  MULTI_USER WITH ROLLBACK IMMEDIATE
--use master
--go
use STGIICDB
go
drop procedure [usp_dropcol]
go
CREATE procedure [usp_dropcol](@tblName varchar(100), @fldName varchar(100))
AS
begin
declare @ret int
declare @sql varchar(200)
set @ret = 0
if exists(select * from syscolumns where lower(name)=lower(@fldName) 
	and lower(object_name(id)) = lower(@tblName))
	
set @ret=1

if @ret=1
	begin
	print 'deleting field'
	set @sql = 'ALTER TABLE '+@tblName+' drop column '+@fldName
	exec(@sql)
	end

end
GO 
drop procedure [usp_addcol]
go
create procedure [usp_addcol](@tblName varchar(100), @fldName varchar(100),@dtype varchar(100))
AS
begin
declare @ret int
set @ret = 0
if exists(select * from syscolumns where lower(name)=lower(@fldName) 
	and lower(object_name(id)) = lower(@tblName))
	
set @ret=1

if @ret=0
	begin
	--print 'adding field'
	exec('ALTER TABLE '+@tblName+' add '+@fldName+' '+@dtype)
	end

end
GO
drop procedure [usp_addPrimaryKey]
GO

create procedure [usp_addPrimaryKey](
		@tblName varchar(30), 
		@fldName varchar(30))
AS
begin
declare @ret int
declare @sql varchar(2000)
declare @pkname varchar(50)

set @ret = 0
set @pkname = 'PK_' + @tblname
if exists(select * from sysobjects where id=object_id(@pkname)) 
	set @ret=1

if @ret=0
	begin
	
	set @sql = 'alter table ' + @tblname + 
		   ' add constraint ' + @pkname  + 
		   ' PRIMARY KEY ('+@fldName+') ' 
	--print @sql
	exec(@sql)
	end

end
GO
declare @tname varchar(100)
declare addpks  CURSOR local  for 
	SELECT name from sysobjects where xtype in( 'U') Order by xtype, name

OPEN addpks
FETCH NEXT FROM addpks into @tname
WHILE @@FETCH_STATUS = 0
BEGIN
	exec [usp_dropcol] @tname,'pkid'
	exec [usp_addcol] @tname,'pkid','int identity not null'
    exec [usp_addPrimaryKey] @tname,'pkid'
	FETCH NEXT FROM addpks 
		into @tname
END

CLOSE addpks
DEALLOCATE addpks
